#13：SnowflakeのALTER TABLE SWAPが超絶的に便利だった件 – Snowflake Advent Calendar 2019 #SnowflakeDB

本エントリはSnowflake Advent Calender 2019のエントリです。

• Snowflake Advent Calendar 2019 - Qiita
• Snowflake Advent Calendar 2019 ｜ シリーズ ｜ DevelopersIO

今回の13日目は、ちょっと細かいですが使ってみて便利だったSQL構文について紹介します。

ALTER TABLE SWAPとは？

一言でいうと、「2つのテーブルを一発で入れ替えるSQL文」です。

• ALTER TABLE | Snowflake SQL Command Reference

データベース運用において、ある集計テーブルを更新する際、古いデータが入っているテーブルとは別に新しい集計データを持つテーブルを作成しておいて、テーブルの入れ替え操作を行う事があります。この方法をとると、データ利用ユーザがテーブルを参照した際にデータが空、もしくは不完全な状態を回避することができます。

基本的には多くのRDBMSで使えるALTER TABLE RENAME文を使い、以下の手順でテーブルの名前を入れ替えることが多いかと思います。

1. テーブルA を テーブルA_tmp など、テーブルB以外の名前に一旦リネーム
2. テーブルB を テーブルA にリネーム
3. テーブルA_tmp を テーブルB にリネーム

しかしこの方法、手数が多く必要で、正直面倒です。そこで、メーカーが固有のSQL文を実装し、テーブル操作の負担を軽減させています。MySQLにはRENAME TABLE文がありますが、それと同じように、Snowflakeには固有のALTER TABLE SWAPがある、という訳です。

テーブルのスワップ(入れ替え)を実施する

では実際にテーブルのスワップを確認します。今回は、Snowflakeのコマンドラインクライアントツール"SnowSQL"から操作しました。

まず、SnowSQLから以下のSQL文を実行し、実験用のテーブルAとテーブルBを作成します。

CREATE TABLE table_a (
  id NUMBER(2),
  data VARCHAR(12)
  );

CREATE TABLE table_b CLONE table_a;

INSERT INTO table_a VALUES (1, 'あいう'), (2, 'えおか');
INSERT INTO table_b VALUES (1, 'ABC'), (2, 'DEF');

SELECT * FROM table_a;
+----+--------+
| ID | DATA   |
|----+--------|
|  1 | あいう |
|  2 | えおか |
+----+--------+

SELECT * FROM table_b;
+----+------+
| ID | DATA |
|----+------|
|  1 | ABC  |
|  2 | DEF  |
+----+------+

では、この2つのテーブルを入れ替えます。

ALTER TABLE table_a SWAP WITH table_b;

+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+

結果を確認します。2テーブルが入れ替わっています。

SELECT * FROM table_a;
+----+------+
| ID | DATA |
|----+------|
|  1 | ABC  |
|  2 | DEF  |
+----+------+

SELECT * FROM table_b;
+----+--------+
| ID | DATA   |
|----+--------|
|  1 | あいう |
|  2 | えおか |
+----+--------+

ついでに実験

ALTER TABLE SWAP文は、内部的にはテーブルのメタ情報はそのままに、瞬間的に中のデータ(マイクロパーティション)を入れ替えているとのことですが、これに関連していくつか気になる点を確認します。

ビューとの関連性を確認する

テーブルにはビューが設定されていることが多いのですが、これが入れ替えでどうなるか確認します。
テーブルA、テーブルBのデータを元に戻し、テーブルAにビューを設定したところで再度テーブルの入れ替えを行います。

CREATE VIEW v_table_a AS SELECT data FROM table_a;

SELECT * FROM v_table_a;
+--------+
| DATA   |
|--------|
| あいう |
| えおか |
+--------+

ALTER TABLE table_a SWAP WITH table_b;

+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+

SELECT * FROM v_table_a;
+------+
| DATA |
|------|
| ABC  |
| DEF  |
+------+

ビューの参照先は、メタ情報が変わってないので、引き続きテーブルAを参照しています(=テーブルBには変わりませんでした)。運用上はこちらの方が都合が良いですよね。

テーブル構造が異なる場合は？

テーブル構造、例えばカラム構造が異なるテーブルもスワップできるか、さらにそこにビューが設定されてる場合はどうなる？ということで調べました。

カラム数が異なるテーブルCを作成し、念の為、テーブルAと、テーブルAに設定されているビューを確認します。

CREATE TABLE table_c (
  id NUMBER(2),
  data VARCHAR(12),
  data2 VARCHAR(12)
  );

INSERT INTO table_c VALUES (1, 'イロハ', 'ニホヘ'), (2, 'トチリ', 'ヌルヲ');

SELECT * FROM table_c;
+----+--------+--------+
| ID | DATA   | DATA2  |
|----+--------+--------|
|  1 | イロハ | ニホヘ |
|  2 | トチリ | ヌルヲ |
+----+--------+--------+

SELECT * FROM table_a;
+----+--------+
| ID | DATA   |
|----+--------|
|  1 | あいう |
|  2 | えおか |
+----+--------+

SELECT * FROM v_table_a;
+--------+
| DATA   |
|--------|
| あいう |
| えおか |
+--------+

さて、ここでテーブルAとテーブルCをスワップさせてみるのですが…。

ALTER TABLE table_a SWAP WITH table_c;

+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+

SELECT * FROM table_a;
+----+--------+--------+
| ID | DATA   | DATA2  |
|----+--------+--------|
|  1 | イロハ | ニホヘ |
|  2 | トチリ | ヌルヲ |
+----+--------+--------+

SELECT * FROM table_c;
+----+--------+
| ID | DATA   |
|----+--------|
|  1 | あいう |
|  2 | えおか |
+----+--------+

なんと、テーブル構造が異なってもスワップできてしまいました。
このようなテーブルにビューはどう対応するのでしょうか？

SELECT * FROM v_table_a;
+--------+
| DATA   |
|--------|
| イロハ |
| トチリ |
+--------+

…なるほど、ビューに仕込まれたSQLの条件を満たすのであれば、参照元テーブルの構造が異なっても問題ないようですね。柔軟性高いです。

まとめ

• ALTER TABLE SWAP文は、テーブルのスワップ(入れ替え)を行うSQL文
• メタ情報はそのままに、データだけを入れ替えるので、ビューの参照先が変わることはない
• テーブルの構造が変わってもスワップ可能、ビューも設定SQLが動作できれば利用可能

個人的にはかなり便利に感じた構文です、上記挙動を知っておいて損は無いと思います。

明日の14日目は、たまちゃんによるパフォーマンス系のエントリをお伝えする予定です。お楽しみに！